# coding=UTF-8
# 求最大id
import re,json

import DB_Driver as DB
from Request_Helper import *

from constant import datais


def sqlr(sql, data):
    m = re.findall(r'(\$\w+)', sql)
    for i in m:
        val = "\"%s\"" % eval("data.%s" % i.replace('$', ''))
        sql = sql.replace(i, val)

    m = re.findall(r'(\#\w+)', sql)
    for i in m:
        val = eval("data.%s" % i.replace('#', ''))
        sql = sql.replace(i, val)

    return sql


def sqlk(sql, **pars):
    m = re.findall(r'(\$\w+)', sql)
    for i in m:
        key = i.replace('$', '')
        val = "\"%s\"" % pars[key]
        sql = sql.replace(i, val)

    m = re.findall(r'(\#\w+)', sql)
    for i in m:
        key = i.replace('#', '')
        val = pars[key]
        sql = sql.replace(i, val)

    return sql


def maxid(tablename, maxfield):
    db =DB.getDB()
    n, res = db.query("select max(%s) as maxid from %s" % (maxfield, tablename))
    # print res
    if res[0]['maxid']:
        return int(res[0]['maxid']) + 1
    else:
        return 1


# 用户基本信息
def get_user_info(id,id_user=-1):
    db =DB.getDB()
    sql = sqlk("select * from user where id=$id",id=id)
    n, rows = db.query(sql)
    rows=out_farmat_users(rows,id=id_user)
    user=out_row_obj(rows)
    sql = sqlk("select * from actor where id=$id",id=id)
    n, rows = db.query(sql)
    rows=out_farmat_actor(rows,id=id_user)
    actor=out_row_obj(rows)
    return {"user":user,"actor":actor}

# 用户信息
def get_user_user(id):
    db =DB.getDB()
    sql = sqlk("select * from user where id=$id",id=id)
    n, rows = db.query(sql)
    rows=out_farmat_users(rows)
    user=out_row_obj(rows)
    return user

# 主播信息
def get_user_actor(id):
    db =DB.getDB()
    sql = sqlk("select * from actor where id=$id",id=id)
    n, rows = db.query(sql)
    rows=out_farmat_actor(rows)
    actor=out_row_obj(rows)
    return actor

# 礼物信息
def get_gift_info(id):
    db =DB.getDB()
    sql = sqlk("select * from gift where id=$id",id=id)
    n, rows = db.query(sql)
    rows=out_farmat_gift(rows)
    rows=out_row_obj(rows)
    return rows

# 用户统计信息
def get_user_counter(id):
    db =DB.getDB()
    sql=sqlk("select *  from user_count  where id_user=$id ",id=id)
    n, rows = db.query(sql)
    rows=out_farmat("user_count",rows)
    return out_row_obj(rows)


# 用户金钱信息
def get_user_finance(id):
    db =DB.getDB()
    # print id
    sql=sqlk("select * from user_finance  where id_user=$id",id=id)
    n, rows = db.query(sql)
    rows=out_farmat("user_finance",rows)
    return out_row_obj(rows)

# 获取该用户关注的用户清单
def get_payattention_list(id_user, id_my=1):
    db =DB.getDB()
    sql=sqlk("select * from user where id  in (select id_b  from user_act where id_a=$id and act=1)",id=id_user)
    n, rows = db.query(sql)
    rows=out_farmat_users(rows,id=id_my)
    return out_row_list(rows)

# 获取关注该用户的粉丝清单
def get_fans_list(id_user,id=1):
    db =DB.getDB()
    sql=sqlk("select * from user where id  in (select id_a  from user_act where id_b=$id and act=1)",id=id_user)
    n, rows = db.query(sql)
    rows=out_farmat_users(rows,id=id)
    return out_row_list(rows)

# 获取主播视频列表
def get_video_list(info):
    db =DB.getDB()
    sql=sqlr("select * from video where id_actor=#id_user and par1=0 order by start_date DESC  limit #cur_page,#pagesize ",info)
    print sql
    n, rows = db.query(sql)
    rows=out_farmat_video(rows)
    return out_row_list(rows)



def get_glist_list():
    db =DB.getDB()
    sql="select * from glist"
    n, rows = db.query(sql)
    rows=out_farmat_glist(rows,1)
    return out_row_list(rows)


def get_glist_item(id,start_pos,size):
    db =DB.getDB()
    # 专题id 查到标签，标签查主播 显示
    sql = 'select * from video WHERE id_actor in (SELECT id_object from object_tag WHERE ctype=0 AND id_tag in (select id_tag from object_tag WHERE ctype=3 and id_object=%s)) and par1=0 order by end_date desc limit %s,%s'%(id,start_pos,size)



    # sql = "select * from video where id in (select id_element from glist_item where id_glist=%s)order by end_date desc limit %s,%s"%(id,start_pos,size)
    # sql="select * from glist_item where id_glist=%s"%(id)
    n, rows = db.query(sql)
    rows=out_farmat_video(rows,0)
    return out_row_list(rows)

# 获取该用户关注的主播的视频
def get_payattention_actor_video_list(info):
    db =DB.getDB()
    sql=sqlr("select * from video where id_actor  in (select id_b  from user_act where id_a=#id_user and act=1) and par1=0 order by ctype ASC, start_date DESC limit #cur_page,#pagesize",info)
    n, rows = db.query(sql)
    rows=out_farmat_video(rows)
    return out_row_list(rows)

# 获取该用户的消费日志
def get_log_pay(info):
    db =DB.getDB()
    sql=sqlr("select * from user_log where id_user=#id_user limit #cur_page,#pagesize",info)
    n, rows = db.query(sql)
    rows=out_farmat_user_log(rows)
    return out_row_list(rows)


def out_farmat(ctype,rows):
    id='id_%s' % ctype
    for row in rows:
        # print ctype
        if ctype == 'user_finance':
            row['pre_rmb'] = row['pre_fmoney'] / 10.0
        row[("id_"+ctype)]=row['id']
        if ctype=="user":
            row.pop('cpwd')
        row.pop('id')
        for item in row:

            # print(item)
            if row[item]==None:
                row[item]=''
    return  rows


def get_ispayattention(id_a,id_b):
    db =DB.getDB()
    sql="select id_a  from user_act where id_a=%s  and id_b=%s and act=1"%(id_a,id_b)
    n,rows=db.query(sql)
    return  True if n>0 else False

def get_location(id_area):
    db =DB.getDB()
    sql="select concat(province,city,area) as area from addr_area as a left join addr_city as b on a.id_city=b.id left join addr_province c on b.id_province=c.id where a.id=%s "%(id_area)
    row=db.queryOne(sql)
    return row['area']

def select_location(area_arr):
    db =DB.getDB()
    sql="select a.id  from addr_area as a left join addr_city as b on a.id_city=b.id left join addr_province c on b.id_province=c.id where c.province=%s and b.city=%s and c.area=%s "%(area_arr[0],area_arr[1],area_arr[2])
    n,rows=db.query(sql)
    if n>0:
        return rows[0]['id']
    else:
        sql="select id  from addr_province c where c.province=%s"%(area_arr[0])
        n,rows1=db.query(sql)
        if n>0:
            sql="select id  from addr_city c where c.city=%s and c.id_province=%s"%(area_arr[1],rows1[0]['id'])
            n,rows2=db.query(sql)
            if n>0:
                id=maxid("addr_area", "id")
                if id<1000000:
                    id=1000000
                sql="insert into addr_area(id,area,id_city) values(%s,'%s',%s)"%(id,area_arr[2],rows2[0]['id'])
                db.execute(sql)
                return id
            else:
                id1=maxid("addr_city", "id")
                if id1<1000000:
                    id1=1000000
                sql="insert into addr_city(id,city,id_province) values(%s,'%s',%s)"%(id1,area_arr[1],rows1[0]['id'])
                db.execute(sql)
                id=maxid("addr_area", "id")
                if id<1000000:
                    id=1000000
                sql="insert into addr_area(id,area,id_city) values(%s,'%s',%s)"%(id,area_arr[2],id1)
                db.execute(sql)
                return id
        else:
            id=maxid("addr_province", "id")
            if id<1000000:
                id=1000000
            sql="insert into addr_province(id,province) values(%s,'%s')"%(id,area_arr[0])
            db.execute(sql)
            id1=maxid("addr_city", "id")
            if id1<1000000:
                id1=1000000
            sql="insert into addr_city(id,city,id_province) values(%s,'%s',%s)"%(id1,area_arr[1],id)
            db.execute(sql)
            id2=maxid("addr_area", "id")
            if id2<1000000:
                id2=1000000
            sql="insert into addr_area(id,area,id_city) values(%s,'%s',%s)"%(id2,area_arr[2],id1)
            db.execute(sql)
            return id2
#
def out_farmat_users(rows,ctype=0,id=-1):

    for row in rows:

        row['id_user']=row['id']
        row.pop('cpwd')
        row.pop('id')
        if row['img']==None or row['img']=="":
            row['img']=''
        else:
            row['img']="%s%s"%(datais.url_img,row['img'])
        if ctype==1:
            row['actor']=get_user_actor(row['id_user'])
        if id!=-1:
            row['ispayattention']=get_ispayattention(id,row['id_user'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows

def out_farmat_gift(rows,ctype=0):

    for row in rows:
        row['id_gift']=row['id']
        row.pop('id')
        if row['img_path']==None or row['img_path']=='':
            row['img_path']=''
            #row['live_picurl']="%s%s"%(datais.url_img,datais.header)
        else:
            row['img_path']="%s%s"%(datais.url_img,row['img_path'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows

def out_farmat_glist(rows,ctype=0):

    for row in rows:
        row['id_glist']=row['id']
        row.pop('id')
        if row['img']==None or row['img']=='':
            row['img']=''
            #row['live_picurl']="%s%s"%(datais.url_img,datais.header)
        else:
            row['img']="%s%s"%(datais.url_img,row['img'])
        if ctype==1:
            row['video_list']=get_glist_item(row['id_glist'],0,2)#get_glist_item(row['id_glist'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows
def out_farmat_glist_item(rows,ctype=0):

    for row in rows:
        row['id_glist_item']=row['id']
        row.pop('id')
        if row['img']==None or row['img']=='':
            row['img']=''
            #row['live_picurl']="%s%s"%(datais.url_img,datais.header)
        else:
            row['img']="%s%s"%(datais.url_img,row['img'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows

def out_farmat_users_info(rows,ctype):

    for row in rows:
        row['id_user']=row['id']
        row.pop('cpwd')
        row.pop('id')
        if row['img']==None or row['img']=="":
            row['img']=''
            #row['live_picurl']="%s%s"%(datais.url_img,datais.header)
        else:
            row['img']="%s%s"%(datais.url_img,row['img'])
        if ctype=='user_finance':
            row['user_finance']=get_user_finance(row['id_user'])
        elif ctype=='user_log':
            row['user_finance']=get_user_finance(row['id_user'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows
def out_farmat_channel(rows):

    for row in rows:
        row['id_channel']=row['id']
        row.pop('id')
        if row['img']==None or row['img']=="":
            row['img']=''
            #row['live_picurl']="%s%s"%(datais.url_img,datais.header)
        else:
            row['img']="%s%s"%(datais.url_img,row['img'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows

def out_farmat_user_log(rows):

    for row in rows:
        row['id_user_log']=row['id']
        row.pop('id')
        row['content']=json.loads(row['content'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows

def out_farmat_actor(rows,id=-1):
    for row in rows:
        row['id_actor']=row['id']
        row.pop('id')
        if row['img_post']==None or row['img_post']=="":
            row['img_post']=''
            #row['live_picurl']="%s%s"%(datais.url_img,datais.header)
        else:
            row['img_post']="%s%s"%(datais.url_img,row['img_post'])
        if id!=-1:
            row['ispayattention']=get_ispayattention(id,row['id_actor'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows


def out_farmat_video(rows,ctype=1):
    for row in rows:
        # print 111
        row['nlooked']=row['nlooked'] * 9
        row['id_video']=row['id']
        row.pop('id')
        # print 111111111111111111,int(row['par2'])
        if row['live_picurl']==None or row['live_picurl']=="":
            row['live_picurl']=''
        elif int(row['par2']) == 1:
            row['live_picurl'] = row['live_picurl']
        else:
            row['live_picurl']="%s%s"%(datais.url_img,row['live_picurl'])

        if row['city']==None or row['city']=="":
            row['city']=''
        elif int(row['par2']) == 1:
            row['city'] = row['city']
            # print 111111111111111,row['city']
        else:
            row['city']="%s%s"%(datais.url_play,row['city'])
        # print row['city']
        # row['location']=get_location(row['id_area'])
        row['location']=row['location'].replace('-','')#"%s%s%s"%(row['l_province'],row['l_city'],row['l_area'])
        # row.pop('id_area')
        if ctype==1:
            row['user']=get_user_user(row['id_actor'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows



def out_farmat_zone_node(rows):
    for row in rows:
        row['id_zone_node']=row['id']
        row.pop('id')
        if row['img']==None or row['img']=="":
            row['img']=''
            #row['img']="%s%s"%(datais.url_img,datais.header)
        else:
            row['img']="%s%s"%(datais.url_img,row['img'])
        row['parm']=json.loads(row['parm'])
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows


#通用,不需id
def out_farmat_common_noid(rows):
    for row in rows:
        row.pop('id')
        for item in row:
            if row[item]==None:
                row[item]=''
    return  rows

def out_row_list(rows):
    if len(rows)>0:
        return rows
    else:
        return []

def out_row_obj(rows):
    if len(rows)>0:
        return rows[0]
    else:
        return {}

def out_farmat_detail(ctype):
    if ctype == 0:
        complain = '广告欺诈'
    elif ctype == 1:
        complain = '淫秽色情'
    elif ctype == 2:
        complain = '骚扰谩骂'
    elif ctype == 3:
        complain = '反动统治'
    else:
        complain = '其他内容'
    return complain

def  out_farmat_trading(rows, type):
    for row in rows:
        row['id_user'] = row['id']
        row.pop('id')
        if row['type'] == 0:
            if type==1:
                if row['money']==6:
                    row['money'] = 42
                if row['money']==18:
                    row['money'] = 126
                if row['money']==30:
                    row['money'] = 210
                if row['money']==108:
                    row['money'] = 756

                if row['money']==518:
                    row['money'] = 3626
            else:
                row['money'] = row['money'] * 10

            row['type'] = '充值'
            row['status'] = '成功'
        elif row['type'] == 8:
            row['money'] = row['money'] * 10
            row['type'] = '充值'
            row['status'] = '成功'
        elif row['type'] == 1:
            row['type'] = '送礼物'
            row['status'] = '成功'

        elif row['type'] == 2:
            row['type'] = '收礼物'
            row['status'] = '成功'
        elif row['type'] == 3:
            row['type'] = '提现'
            if row['status'] == 0:
                row['status'] = '审核中'
            elif row['status'] == 1:
                row['status'] = '通过'
            elif row['status'] == 2:
                row['status'] = '未通过'
                row['status'] = '未通过'

    return rows
if __name__ == "__main__":
    print maxid("gift", 'id')
    print maxid("glist", 'id')
    info = INFO(None)
    info.aa = 'student'
    info.bb = '101'
    sql = "select * from #aa where id=$bb"
    print sqlr(sql, info)

    print sqlk(sql, aa='stu', bb='102')
